﻿Imports System.Data.OleDb
Module modDisplayCandidates
    ''The listview is provided as a referenced argument
    Public Sub PopulateCandidatesListFromAccess(ByRef lv As ListView)
        'Create a connection string to the MS Access database.
        Dim connModCandidates As New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;Data Source=|DataDirectory|\SkyCoffee.mdb")

        'Create a connection to the database
        'Dim conn As New System.Data.OleDb.OleDbConnection(connectionString)
        Dim com As System.Data.OleDb.OleDbCommand
        Dim reader As System.Data.OleDb.OleDbDataReader

        Try
            'Open the connection
            connModCandidates.Open()
            'Create a new instance of the command and provide the SELECT query, and the opened connection
            com = New System.Data.OleDb.OleDbCommand("SELECT * FROM tblCandidates", connModCandidates)
            reader = com.ExecuteReader(CommandBehavior.CloseConnection)

            'Check to see if the SELECT query returned any rows
            If reader.HasRows Then
                'If so, perform a read for each row
                While reader.Read
                    'Declare a new ListViewItem, and provide the information
                    'to be shown in the very first column
                    Dim item As New ListViewItem(reader.Item("fldCandidateReferenceNumber").ToString)

                    'Decare a new ListViewSubItem, and provide the information
                    'to be shown in the second (and so forth) column
                    Dim subItemApplicationStatus As New ListViewItem.ListViewSubItem
                    subItemApplicationStatus.Text = reader.Item("fldApplicationStatus").ToString

                    Dim subItemCandidateFirstName As New ListViewItem.ListViewSubItem()
                    subItemCandidateFirstName.Text = reader.Item("fldCandidateFirstName").ToString

                    Dim subItemCandidateSurname As New ListViewItem.ListViewSubItem()
                    subItemCandidateSurname.Text = reader.Item("fldCandidateSurname").ToString

                    Dim subItemCandidateGender As New ListViewItem.ListViewSubItem
                    subItemCandidateGender.Text = reader.Item("fldCandidateGender").ToString

                    Dim subItemCandidateAge As New ListViewItem.ListViewSubItem
                    subItemCandidateAge.Text = reader.Item("fldCandidateAge").ToString

                    Dim subItemCandidateCity As New ListViewItem.ListViewSubItem
                    subItemCandidateCity.Text = reader.Item("fldCandidateCity").ToString

                    Dim subItemDesiredFunctionalArea As New ListViewItem.ListViewSubItem
                    subItemDesiredFunctionalArea.Text = reader.Item("fldDesiredFunctionalArea").ToString

                    Dim subItemDesiredKeySkills As New ListViewItem.ListViewSubItem()
                    subItemDesiredKeySkills.Text = reader.Item("fldDesiredKeySkills").ToString

                    Dim subItemDesiredAvailability As New ListViewItem.ListViewSubItem()
                    subItemDesiredAvailability.Text = reader.Item("fldDesiredAvailability").ToString

                    Dim subItemDesiredEmploymentType As New ListViewItem.ListViewSubItem()
                    subItemDesiredEmploymentType.Text = reader.Item("fldDesiredEmploymentType").ToString

                    Dim subItemCandidateMobilePhone As New ListViewItem.ListViewSubItem()
                    subItemCandidateMobilePhone.Text = reader.Item("fldCandidateMobilePhone").ToString

                    'Add the ListViewSubItem to the ListViewItem
                    item.SubItems.Add(subItemApplicationStatus)
                    item.SubItems.Add(subItemCandidateFirstName)
                    item.SubItems.Add(subItemCandidateSurname)
                    item.SubItems.Add(subItemCandidateGender)
                    item.SubItems.Add(subItemCandidateAge)
                    item.SubItems.Add(subItemCandidateCity)
                    item.SubItems.Add(subItemDesiredFunctionalArea)
                    item.SubItems.Add(subItemDesiredKeySkills)
                    item.SubItems.Add(subItemDesiredAvailability)
                    item.SubItems.Add(subItemDesiredEmploymentType)
                    item.SubItems.Add(subItemCandidateMobilePhone)

                    'Add the ListViewItem to the ListView
                    lv.Items.Add(item)

                    'Repeat until all rows have been read
                End While
            End If

            'Close the reader
            reader.Close()
        Catch ex As Exception
            'If something went sideways, make sure that you close the connection
            'before exiting the method.
            If connModCandidates.State = ConnectionState.Open Then
                connModCandidates.Close()
            End If
        End Try
    End Sub

End Module
